package cn.ztydata.teachingsystem.heaven.dao;


import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.cons.GroupType;
import cn.ztydata.teachingsystem.heaven.entity.Student;
import cn.ztydata.teachingsystem.heaven.entity.User;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.data.redis.connection.srp.SrpConnection;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;

/**
 * 学生dao
 * <p/>
 * Created by wyh on 2015/3/20.
 */
@Repository
public class StudentDao extends BaseDao {


    //<editor-fold "课程群组">

    /**
     * 获取与数据库中重复的学号
     *
     * @param studentNumSet 学生学号集合
     * @return String
     * @author wyh
     * @since 2015-03-20
     */

    public String getDuplicateNum(final Set<String> studentNumSet) {
        if (studentNumSet.isEmpty()) {
            log.debug("导入的学生学号集合为空");
            log.info("学生学号集合为0");
            return null;
        }

        try {

            String sql = "SELECT sNumber FROM t_student WHERE sNumber IN (:numSet) LIMIT 1";
            SqlParameterSource sps = new MapSqlParameterSource().addValue("numSet", studentNumSet);

            log.debug("return 表中与导入数据重复的学生学号");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("数据库中无重复的学生学号");

            log.info("数据库中无重复的学生学号");
            return null;
        }
    }

    /**
     * 批量新增学生
     *
     * @param studentList 学生信息集合
     * @return int[]
     * @author wyh
     * @since 2015-03-20
     */
    public int[] saveStudents(final List<Student> studentList) {
        String sql = "INSERT INTO t_student(sNumber, sName, sPinyin, sDeptNum, dtBirthday, tiSex, sSpecNum, sClassNum, dtEntryDate) " +
                "VALUES(:number, :name, :pinyin, :deptNum, :birthday, :sex, :specNum, :classNum, :entryDate)";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(studentList.toArray());
        log.debug("return 批量导入学生数据的结果");
        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    /**
     * 批量更新学生
     *
     * @param studentList 学生信息集合
     * @return int[]
     * @author wyh
     * @since 2015-03-20
     */
    public int[] updateStudents(final List<Student> studentList) {
        String sql = "UPDATE t_student SET sDeptNum = :deptNum, sSpecNum = :specNum, sClassNum = :classNum WHERE sNumber = :number";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(studentList.toArray());
        log.debug("return 批量更新学生数据的结果");
        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    /**
     * 学生列表
     *
     * @param depart   系部
     * @param major    专业
     * @param search   名字
     * @param page     页数
     * @param pageSize 条数
     * @return Page
     * @author wyh
     * @since 2015-03-23
     */
    public Page<Student> listStudents(String depart, String major, String search, int page, int pageSize) {
        String where = " WHERE 1=1";

        MapSqlParameterSource sps = new MapSqlParameterSource();

        //系部查询
        if (!depart.isEmpty()) {
            log.debug("系部不为空：!StringUtils.isEmpty(depart)");
            where += " AND sDeptNum = :depart ";
            sps.addValue("depart", depart);
        }

        //专业查询
        if (!major.isEmpty()) {
            log.debug("专业不为空:!major.isEmpty()");
            where += " AND sSpecNum = :major ";
            sps.addValue("major", major);
        }

        //姓名、学号
        if (!StringUtils.isEmpty(search)) {
            log.debug("搜索关键字不为空：!StringUtils.isEmpty(search)");
            where += " AND (UPPER(sName) LIKE :search OR UPPER(sNumber) LIKE :search) ";
            sps.addValue("search", "%" + search.toUpperCase() + "%");
        }

        String totalSql = "SELECT COUNT(id) COUNT FROM t_student";

        //总数
        int total = namedParameterJdbcTemplate.queryForObject(totalSql + where, sps, Integer.class);
        if (total == 0) {
            log.debug("查询总数为0：total == 0");
            return new Page<>();
        }

        String dataSql = "SELECT id,sNumber,sName,tiSex,dtBirthday,dtEntryDate,sDeptNum,sSpecNum,sTel,sClassNum FROM t_student" + where + getLimitStr(page, pageSize);

        //查询
        List<Student> studentList = namedParameterJdbcTemplate.query(dataSql.toString(), sps, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
                Student student = new Student();

                student.setId(rs.getLong("id"));
                student.setNumber(rs.getString("sNumber"));
                student.setName(rs.getString("sName"));
                student.setSex(rs.getInt("tiSex"));
                student.setBirthday(rs.getDate("dtBirthday"));
                student.setEntryDate(rs.getDate("dtEntryDate"));
                student.setDeptNum(rs.getString("sDeptNum"));
                student.setSpecNum(rs.getString("sSpecNum"));
                student.setTel(rs.getString("sTel"));
                student.setClassNum(rs.getString("sClassNum"));

                return student;
            }
        });

        log.debug("返回 学生列表");
        return new Page<>(total, pageSize, page, studentList);
    }

    /**
     * 学生详细
     *
     * @param id 学生ID
     * @return Student
     * @author wyh
     * @since 2015-03-23
     */
    public Student getDetail(long id) {
        String sql = "SELECT st.*, d.sName sDept, sp.sName sSpec,st.sClassNum FROM t_student st LEFT JOIN t_department d ON sDeptNum=d.sNumber " +
                "LEFT JOIN t_specialty sp ON sSpecNum=sp.sNumber WHERE st.id = :id";

        try {
            log.debug("返回 查询学生个人明细");
            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", id), new RowMapper<Student>() {
                @Override
                public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Student student = new Student();

                    student.setId(rs.getLong("id"));
                    student.setNumber(rs.getString("sNumber"));
                    student.setName(rs.getString("sName"));
                    student.setSex(rs.getInt("tiSex"));
                    student.setBirthday(rs.getDate("dtBirthday"));
                    student.setEntryDate(rs.getDate("dtEntryDate"));
                    student.setDepart(rs.getString("sDept"));
                    student.setMajor(rs.getString("sSpec"));
                    student.setTel(rs.getString("sTel"));
                    student.setAddress(rs.getString("sAddress"));
                    student.setAvatar(rs.getString("sAvatar"));
                    student.setRemark(rs.getString("sRemark"));
                    student.setJob("学生");
                    student.setClassNum(rs.getString("sClassNum"));

                    return student;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("查询学生个人明细为空，返回null");
            return null;
        }
    }

    /**
     * 更改学生信息
     *
     * @param student 学生
     * @return int
     * @author wyh
     * @since 2015-03-23
     */
    public int updateStudent(Student student) {
        String sql = "UPDATE t_student SET tiSex=:sex, dtBirthday=:birthday, sAddress=:address, sTel=:tel, sAvatar=:avatar, sRemark=:remark WHERE id=:id";
        SqlParameterSource sps = new BeanPropertySqlParameterSource(student);

        log.debug("返回 更改学生信息的结果");
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 更改密码
     *
     * @param id  学生ID
     * @param pwd 密码
     * @return int
     * @author wyh
     * @since 2015-03-23
     */
    public int updatePwd(long id, String pwd) {
        String sql = "UPDATE t_student SET sPassword=MD5(:pwd) WHERE id=:id";

        log.debug("返回 更改学生密码的结果");
        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource().addValue("id", id).addValue("pwd", pwd));
    }

    /**
     * 检查密码
     *
     * @param id  ID
     * @param pwd 密码
     * @return boolean
     * @author wyh
     * @since 2015-03-23
     */
    public boolean checkPwd(long id, String pwd) {
        String sql = "SELECT COUNT(*) FROM t_student WHERE id=:id AND sPassword=MD5(:pwd)";

        log.debug("返回 检查密码结果");
        return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", id).addValue("pwd", pwd), Integer.class) > 0;
    }

    /**
     * 查询学生姓名与头像
     *
     * @param studentIds 教师ID集合
     * @return List
     * @author lyj
     * @since 2015-3-20
     */
    public List<Student> getPicById(final Set<Long> studentIds) {
        String sql = "SELECT id, sName, sAvatar FROM t_student WHERE id IN (:studentIds)";

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("studentIds", studentIds);

        log.debug("查询学生姓名与头像");
        return namedParameterJdbcTemplate.query(sql, param, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
                Student entity = new Student();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAvatar(rs.getString("sAvatar"));

                return entity;
            }
        });
    }


    /**
     * 查询学生姓名与头像
     *
     * @param studentIds 教师ID集合
     * @return List
     * @author wyh
     * @since 2015-03-25
     */
    public List<Student> getAllForPic(Set<Long> studentIds) {
        String sql = "SELECT id,sName,sAvatar FROM t_student WHERE id IN (:studentIds)";

        log.debug("查询学生姓名与头像");

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("studentIds", studentIds), new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
                Student entity = new Student();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAvatar(rs.getString("sAvatar"));

                return entity;
            }
        });
    }


    /**
     * 分页获取课程群组同学列表
     *
     * @param courseCode 课程代码
     * @param pageNum    当前页码
     * @param pageSize   每页条数
     * @param letter     姓名首字母
     * @param name       姓名
     * @return page
     * @author lyj
     * @since 2015-4-1
     */
    public Page<Student> getGroupList(final String courseCode, final int pageNum, final int pageSize, final String letter, final String name) {
        StringBuilder sql = new StringBuilder("SELECT stu.id, stu.sName as stuName, stu.sNumber,spe.sName as speName, stu.sAvatar FROM t_student stu " +
                "JOIN t_student_course sc ON stu.sNumber = sc.sStudentNum " +
                "LEFT JOIN t_specialty spe ON stu.sSpecNum = spe.sNumber ");

        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(*) FROM t_student stu " +
                "JOIN t_student_course sc ON stu.sNumber = sc.sStudentNum " +
                "LEFT JOIN t_specialty spe ON stu.sSpecNum = spe.sNumber");

        StringBuilder where = new StringBuilder(" WHERE 1 = 1");

        MapSqlParameterSource param = new MapSqlParameterSource();

        //过滤课程代码
        if (!courseCode.isEmpty()) {
            log.debug("过滤课程代码!courseCode.isEmpty()");
            where.append(" AND sc.sCourseCode = :courseCode");
            param.addValue("courseCode", courseCode);
        }

        //过滤姓名首字母
        if (!letter.equals("0")) {
            log.debug("过滤姓名首字母!letter.equals(\"0\")");
            where.append(" AND UPPER(stu.sPinyin) LIKE :letter");
            param.addValue("letter", letter.toUpperCase() + "%");
        }

        //过滤姓名
        if ((!name.isEmpty()) && name != null) {
            log.debug("过滤姓名(!name.isEmpty()) && name != null");
            where.append(" AND (stu.sName LIKE :name OR stu.sNumber LIKE :name)");
            param.addValue("name", "%" + name + "%");
        }

        //获取查询总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        //获取总条数
        if (total == 0) {
            log.debug("获取总条数total == 0");
            return new Page<>();
        }

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        //获取习题列表（习题ID，题干，习题类型，难度）
        List<Student> studentList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();

                student.setId(resultSet.getLong("id"));
                student.setName(resultSet.getString("stuName"));
                student.setNumber(resultSet.getString("sNumber"));
                student.setMajor(resultSet.getString("speName"));
                student.setAvatar(resultSet.getString("sAvatar"));

                return student;
            }
        });


        log.debug("分页获取课程群组同学列表");
        return new Page<>(total, pageSize, pageNum, studentList);

    }


    /**
     * 根据课程代码获取所有学生列表
     *
     * @param courseCode 课程代码
     * @return List
     * @author lyj
     * @since 2015-4-2
     */
    public List<Student> getAllByCourseCode(final String courseCode) {
        StringBuilder sql = new StringBuilder(" SELECT stu.id, stu.sName, stu.sAvatar FROM t_student stu JOIN t_student_course sc ON stu.sNumber = sc.sStudentNum  WHERE sc.sCourseCode = :courseCode");

        log.debug("根据课程代码获取所有学生列表");
        return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("courseCode", courseCode), new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();

                student.setId(resultSet.getLong("id"));
                student.setName(resultSet.getString("sName"));
                student.setAvatar(resultSet.getString("sAvatar"));

                return student;
            }
        });
    }


    /**
     * 学生活跃度统计
     *
     * @param creatorId 创建人ID
     * @return int
     * @author lyj
     * @since 2015-4-2
     */
    public int getCountAll(final long creatorId, final long groupId, final String courseCode) {
        StringBuilder sql = new StringBuilder("SELECT speakCount+noteCount+resourceCount+questionCount AS countAll FROM " +
                "(SELECT " +
                "(SELECT COUNT(s.id) FROM t_speak s WHERE s.iCreatorId = :creatorId AND s.tiRole = :role AND s.iGroupId = :groupId AND s.tiGroupType = :groupType) speakCount, " +
                "(SELECT COUNT(n.id) FROM t_note n WHERE n.iCreatorId = :creatorId AND n.tiRole = :role AND n.sCourseCode = :courseCode ) noteCount," +
                "(SELECT COUNT(r.id) FROM t_resource r WHERE r.iUploaderId = :creatorId AND r.tiRole = :role AND r.iGroupId = :groupId AND r.tiGroupType = :groupType) resourceCount," +
                " COUNT(q.id) questionCount FROM t_question q WHERE q.iCreatorId = :creatorId AND q.tiRole = :role AND q.iGroupId = :groupId ) temp");

        log.debug("学生度统计");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource()
                .addValue("creatorId", creatorId)
                .addValue("role", 1)
                .addValue("groupId", groupId)
                .addValue("courseCode", courseCode)
                .addValue("groupType", GroupType.COURSE_GROUP)
                , Integer.class);

    }


    /**
     * 验证用户登录信息
     *
     * @param user 登录信息实体类
     * @return User
     * @author fanruiping
     * @since 2015-04-07
     */
    public Student checkNumberAndPassword(User user) {
        try {
            String sql = "SELECT * FROM t_student WHERE sNumber=:number AND sPassword=:password LIMIT 1";

            MapSqlParameterSource params = new MapSqlParameterSource().addValue("number", user.getNumber()).addValue("password", user.getPassword());

            return namedParameterJdbcTemplate.queryForObject(sql, params, new RowMapper<Student>() {
                @Override
                public Student mapRow(ResultSet rs, int i) throws SQLException {
                    Student entity = new Student();

                    entity.setId(rs.getLong("id"));
                    entity.setName(rs.getString("sName"));
                    entity.setDeptNum(rs.getString("sDeptNum"));
                    entity.setSpecNum(rs.getString("sSpecNum"));
                    entity.setEntryDate(rs.getDate("dtEntryDate"));
                    entity.setBirthday(rs.getDate("dtBirthday"));
                    entity.setClassNum(rs.getString("sClassNum"));
                    entity.setAddress(rs.getString("sAddress"));
                    entity.setTel(rs.getString("sTel"));
                    entity.setSex(rs.getInt("tiSex"));
                    entity.setAvatar(rs.getString("sAvatar"));
                    entity.setRemark(rs.getString("sRemark"));

                    return entity;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("根据学号和密码未获取到指定学生信息:EmptyResultDataAccessException ex");

            return null;
        }
    }

    /**
     * ID查询学号
     *
     * @param id
     * @return String
     * @author wyh
     * @since 2015-04-02
     */
    public String getNumById(long id) {
        String sql = "SELECT sNumber FROM t_student WHERE id=:id";
        try {
            log.debug("返回 学号");
            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", id), String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("查询学号为空 返回null");
            return null;
        }
    }

    //</editor-fold>


    //<editor-fold "班级群组">

    /**
     * 班级群组活跃学生统计
     *
     * @param groupId   群组ID <=> 班级ID
     * @param creatorId 创建者ID
     * @return int
     * @author lyj
     * @since 2015-5-5
     */
    public int getClassCountAll(final long groupId, final long creatorId) {
        StringBuilder sql = new StringBuilder("SELECT speakCount+resourceCount AS countAll FROM " +
                "(SELECT " +
                "(SELECT COUNT(s.id) FROM t_speak s WHERE s.iCreatorId = :creatorId AND s.tiRole = :role AND s.iGroupId = :groupId AND s.tiGroupType = :groupType) speakCount," +
                "COUNT(r.id) AS resourceCount FROM t_resource r WHERE r.iUploaderId = :creatorId AND r.tiRole = :role AND r.iGroupId = :groupId AND r.tiGroupType = :groupType) temp");

        log.debug("班级群组活跃学生统计");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource()
                .addValue("role", 1)
                .addValue("creatorId", creatorId)
                .addValue("groupId", groupId)
                .addValue("groupType", GroupType.CLASS_GROUP)
                , Integer.class);

    }


    /**
     * 根据班级代码获取所有学生列表
     *
     * @param classCode 课程代码
     * @return List
     * @author lyj
     * @since 2015-4-2
     */
    public List<Student> getAllByClassCode(final String classCode) {
        StringBuilder sql = new StringBuilder(" SELECT id, sName, sAvatar FROM t_student WHERE sClassNum = :classNum");

        log.debug("根据班级代码获取所有学生列表");
        return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("classNum", classCode), new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();

                student.setId(resultSet.getLong("id"));
                student.setName(resultSet.getString("sName"));
                student.setAvatar(resultSet.getString("sAvatar"));

                return student;
            }
        });
    }

    /**
     * 根据班级代码分页获取班级同学列表
     *
     * @param classNum 班级代码
     * @param letter   姓名首字母
     * @param pageNum  当前页码
     * @param pageSize 每页条数
     * @param search   关键字
     * @return page
     * @author lyj
     * @since 2015-5-6
     */
    public Page<Student> getSchoolMate(final String classNum, final String letter, final int pageNum, final int pageSize, final String search) {
        StringBuilder sql = new StringBuilder("SELECT id, sName, sNumber, sAvatar, tiSex FROM t_student ");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(*) FROM t_student");
        StringBuilder where = new StringBuilder(" WHERE 1 = 1");

        MapSqlParameterSource param = new MapSqlParameterSource();

        //过滤课程代码
        if (!classNum.isEmpty()) {
            log.debug("!classNum.isEmpty(),过滤课程代码");
            where.append(" AND sClassNum = :classNum");
            param.addValue("classNum", classNum);
        }

        //过滤姓名首字母
        if (!letter.equals("0")) {
            log.debug("!letter.equals('0'),过滤姓名首字母");
            where.append(" AND UPPER(sPinyin) LIKE :letter");
            param.addValue("letter", letter.toUpperCase() + "%");
        }

        //过滤姓名
        if (!search.isEmpty()) {
            log.debug("!search.isEmpty(),过滤姓名");
            where.append(" AND (sName LIKE :search OR sNumber LIKE :search)");
            param.addValue("search", "%" + search + "%");
        }

        //获取查询总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        //获取总条数
        if (total == 0) {
            log.debug("total == 0,获取总条数");
            return new Page<>();
        }

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        List<Student> studentList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();

                student.setId(resultSet.getLong("id"));
                student.setName(resultSet.getString("sName"));
                student.setNumber(resultSet.getString("sNumber"));
                student.setAvatar(resultSet.getString("sAvatar"));
                student.setSex(resultSet.getInt("tiSex"));

                return student;
            }
        });

        log.debug("3.根据班级代码分页获取班级同学列表");
        return new Page<>(total, pageSize, pageNum, studentList);
    }
    //</editor-fold>


    /**
     * 校验指定班级下含有学生
     *
     * @param classNumber 班级代码
     * @return boolean
     * @author fanruiping
     * @since 2015-05-05
     */
    public boolean checkClassHasStudent(String classNumber) {
        try {
            String sql = "SELECT id FROM t_student WHERE sClassNum=:number LIMIT 1";

            log.debug("指定班级下含有学生数据");

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("number", classNumber), Long.class) > 0;
        } catch (EmptyResultDataAccessException ex) {
            log.debug("指定班级下不含学生数据");

            return false;
        }
    }
}
